﻿using DataAccessObject;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MyExcel = Microsoft.Office.Interop.Excel;


namespace BUSINESS.ExcelService
{
    public class HocSinhExcel
    {
        public static List<HOCSINH> ReadHocSinhFromFile(string pathName)
        {
            List<HOCSINH> listHocSinh = new List<HOCSINH>();
            var excelApp = new MyExcel.Application();

            MyExcel._Workbook workbook = excelApp.Workbooks.Open(pathName);
            MyExcel._Worksheet worksheet = workbook.Sheets[1];

            //get used range
            MyExcel.Range range = worksheet.UsedRange;
            int rowCount = range.Rows.Count;
            int colCount = range.Columns.Count;

            //first row for header 
            //therefore, we read data from second row
            for (int i = 2; i <= rowCount; i++)
            {
                //read database                
                string hoTen = (string)worksheet.Cells[i, 2].Value;
                DateTime ngaySinh = (DateTime)worksheet.Cells[i, 3].Value; //type date, we will change later
                string gioiTinh = (string)worksheet.Cells[i, 4].Value;
                string queQuan = (string)worksheet.Cells[i, 5].Value;
                string diaChi = (string)worksheet.Cells[i, 6].Value;
                DateTime ngayNhapHoc = (DateTime)worksheet.Cells[i, 7].Value; //type date, we will change later
                string nguoiBaoHo = (string)worksheet.Cells[i, 8].Value;
                //use these data to create a new HOCSINH
                HOCSINH hs = new HOCSINH();
                hs.HoTen = hoTen;
                hs.NgaySinh = ngaySinh;
                if (gioiTinh.Equals("Nam"))
                {
                    hs.GioiTinh = true;
                }
                else
                {
                    hs.GioiTinh = false;
                }
                
                hs.QueQuan = queQuan;
                hs.DiaChi = diaChi;
                hs.NgayNhapHoc = ngayNhapHoc;
                hs.NguoiBaoHo = nguoiBaoHo;
                listHocSinh.Add(hs);
            }
            workbook.Close();
            return listHocSinh;
        }

        public static void WriteToFile(string fileName, GIAOVIEN giaovien, LOPHOC lophoc, List<HOCSINH> listHocSinh)
        {
            var excelApp = new MyExcel.Application();
            excelApp.Visible = true;

            FileStream file = File.Create(fileName);
            string pathName = file.Name;
            file.Close();

            excelApp.Workbooks.Open(pathName);
            MyExcel._Worksheet worksheet = excelApp.ActiveSheet;
            //Ghi ten truong
            MyExcel.Range rangeTruong = worksheet.get_Range("A1", "J1");
            rangeTruong.Merge(Type.Missing);
            worksheet.Cells[1, 1] = "Trường THPT Quỳnh Lưu 2";
            rangeTruong.HorizontalAlignment = MyExcel.XlHAlign.xlHAlignCenter;
            rangeTruong.Font.Bold = true;
            rangeTruong.Font.Size = 17;
            //ghi tên lớp và giáo viên

            MyExcel.Range rangeLop = worksheet.get_Range("A2", "B2");
            rangeLop.Merge(Type.Missing);
            rangeLop.Font.Bold = true;
            worksheet.Cells[2, 1] = "Lớp: " + lophoc.TenLopHoc;

            MyExcel.Range rangeGVCN = worksheet.get_Range("C2", "E2");
            rangeGVCN.Merge(Type.Missing);
            rangeGVCN.Font.Bold = true;
            worksheet.Cells[2, 3] = "Giáo Viên Chủ Nhiệm: " + giaovien.HoTen;
            //ghi header cho danh sách học sinh 
            string[] listheader = { "STT", "Mã Học Sinh", "Họ Tên", "Ngày Sinh", "Giới Tính", "Quê Quán", "Địa Chỉ", "Ngày Nhập Học", "Ngày Nghỉ Học", "Người Bảo Hộ" };
            for (int i = 0; i < listheader.Length; i++)
            {
                worksheet.Cells[4, i + 1] = listheader[i];
            }

            int row = 4;
            foreach (HOCSINH hs in listHocSinh)
            {
                row++;
                worksheet.Cells[row, 1] = row - 3;
                worksheet.Cells[row, 2] = hs.MaHocSinh;
                worksheet.Cells[row, 3] = hs.HoTen;
                worksheet.Cells[row, 4] = hs.NgaySinh;                 
                if (hs.GioiTinh)
                {
                    worksheet.Cells[row, 5] = "Nam";
                }
                else
                {
                    worksheet.Cells[row, 5] = "Nữ";
                }
                worksheet.Cells[row, 6] = hs.QueQuan;
                worksheet.Cells[row, 7] = hs.DiaChi;
                worksheet.Cells[row, 8] = hs.NgayNhapHoc;
                worksheet.Cells[row, 9] = hs.NgayNghiHoc;
                worksheet.Cells[row, 10] = hs.NguoiBaoHo;
                
            }
            for (int i = 0; i < listheader.Length; i++)
            {
                worksheet.Columns[i + 1].AutoFit();
            }
            string lastCell = "J" + row;
            worksheet.Range["A4", lastCell].AutoFormat(MyExcel.XlRangeAutoFormat.xlRangeAutoFormatReport1);

        }
    }
}
